SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_PlazaDuenoActual
AS
SELECT DISTINCT 
                      TOP 100 PERCENT p.idPlaza, p.CUPOF, s.Agente AS duenoactual, s.SituacionRevista AS sitrevduenoactual, 
                      s.CategoriaTitulo AS categoriatituloactualocupante
FROM         dbo.tb_Plazas p LEFT OUTER JOIN
                      dbo.tb_SubserviciosAgentes ss ON ss.Plaza = p.idPlaza AND ss.FechaAlta <= GETDATE() AND (ss.Baja = 'N' OR
                      ss.Baja = 'S' AND ss.FechaBaja >= GETDATE()) INNER JOIN
                      dbo.tb_ServiciosAgentes s ON ss.ServicioAgente = s.idServicioAgente
WHERE     (p.Baja = 'N') AND (s.Agente IS NULL) OR
                      (p.Baja = 'N') AND (s.SituacionRevista IN (1, 2))
ORDER BY p.CUPOF

GO
GRANT SELECT ON  [dbo].[vw_PlazaDuenoActual] TO [SoloVer]
GO
